Dog-Sitting App — Worked Example

Rover/Wag-style · US launch, 5M owners, 5M sitters · 250k sits/week · 2.5M searches/week · geo + time search · no double-booking · a teaching walkthrough, not a cheatsheet.

1. Requirements

Restate the problem first, because the restatement earns you the right to make assumptions. "We're building a marketplace where dog owners find and book sitters. An owner searches for sitters available in a time window near them and manages their upcoming bookings. A sitter publishes availability and rates and sees their upcoming bookings. Later we'll add recurring walks with a pickup-or-dropoff choice and a lockbox handoff."

The functional scope worth committing to out loud: an owner searches by location and time window and does CRUD on bookings; a sitter adds and removes availability and rates and views bookings. The extension adds recurring walks (weekly MWF or daily), a choice to drop the dog at the sitter's place or have the sitter pick it up, and a lockbox code when they pick up. State what you're cutting, too — payments UI, messaging, reviews, and the categorization of breeds are all out of scope unless they ask.

The non-functional requirements are what actually shape the design, and there are two that matter. Search is a geospatial-plus-time query, and it's the read hot path. Booking needs consistency so a sitter is never double-booked for overlapping times. Everything else follows from those two.

2. Architecture

The architecture has three ideas, and each is worth a sentence of defense rather than just a box.

Idea one: search reads from a derived index, not the source of truth. A sitter's location is static, but their availability changes constantly as bookings come and go. So the searchable copy — a denormalized Elasticsearch document holding location, filters, ratings and current availability — is a read model kept fresh by CDC from the sitter and bookings databases, exactly like the tweet-search index. It's eventually consistent by seconds, which is fine for browsing because the booking step re-checks the authoritative database before committing. A stale "available" in search just yields an occasional "sorry, just taken" at booking time, never a double-booking.

Idea two: the write path is separate and owns consistency. Booking goes through its own service into a bookings database, and that database — not application code — is what guarantees no overlap. Keeping the write path distinct from the read path means the heavy search traffic never contends with the correctness-critical booking transaction.

Idea three: shard by the thing you transact against. The bookings table is sharded by sitterId, so every booking for one sitter lives on one shard. That makes the overlap check and the insert a single-shard transaction with no distributed commit — the ACID-plus-colocation lesson made concrete. We'll lean on this in §6.

3. Core entities

Sitter {
  id, name, location: (lat,lng),
  geoCell,            // S2/geohash id
  rates, serviceTypes,
  willTravel, travelRadius
}
Availability {
  sitterId,
  during: tstzrange   // [start, end)
}
Booking {
  id, ownerId, sitterId,
  during: tstzrange,  // the reserved slot
  status: pending|confirmed|cancelled|expired,
  reservedUntil,      // set while pending
  serviceType, locationOption  // sit|walk
}                              // dropoff|pickup

4. Geospatial search (the read hot path)

The query is "sitters within X miles of me, available between T1 and T2." You cannot scan a million sitters and compute distance to each on every search, so you need a spatial index. The shared idea behind all of them is to convert a 2D location into a locality-preserving 1D key, so physically-near sitters share a key prefix and become range-scannable.

Why Elasticsearch is the right search-optimized database here

Say the reason out loud, because "I'd use Elasticsearch" without one is a name-drop. Sitter search is never only a geo query — it's proximity AND filters AND ranking, resolved in a single pass. "Boarding, large dogs OK, under $60/night, within 5 miles, sorted by rating." A pure spatial structure (geohash in Redis, S2 cells in a KV store) gives you the radius and then leaves you to filter and rank in application code over the candidate set. Elasticsearch does all three in the engine: geo_point fields indexed as BKD trees for the distance cut, an inverted index for the categorical filters, and a scoring pass over the survivors. That combination is the justification.

PUT /sitters/_doc/1234
{
  "sitter_id": 1234,
  "loc": { "lat": 37.77, "lon": -122.41 },  // geo_point
  "services": ["boarding","walking"],
  "dog_sizes": ["small","large"],
  "nightly_rate_cents": 5500,
  "rating_avg": 4.62, "rating_count": 1043,
  "available": [ {"gte":"2026-08-08","lt":"2026-08-11"} ]
}

GET /sitters/_search
{ "query": { "bool": {
  "filter": [
    { "geo_distance": { "distance": "5mi",
        "loc": {"lat":37.77,"lon":-122.41} } },
    { "term":  { "services": "boarding" } },
    { "term":  { "dog_sizes": "large" } },
    { "range": { "nightly_rate_cents": {"lte":6000} } }
  ],
  "should": [ { "rank_feature":
      { "field": "rating_avg" } } ]
}}}

What "CDC → refresh search index" actually means

Two different things get collapsed into that arrow on the diagram, and separating them is a small piece of depth worth having ready.

The pipeline sense. Postgres commits a write. Debezium (or equivalent) tails the WAL and emits a change event. A consumer rebuilds the affected sitter's denormalized document and issues a bulk upsert into Elasticsearch. Nothing writes to Elasticsearch directly — the search index is a derived store, and treating it as a second source of truth is how you get two systems that disagree with no way to reconcile.

The Elasticsearch sense, which is a real technical term. An indexed document is not immediately searchable. It lands in an in-memory buffer plus a translog. A refresh flushes that buffer into a new Lucene segment and opens it for reading, which is the moment the document becomes visible to queries. The default refresh_interval is 1 second — this is why Elasticsearch is described as near-real-time rather than real-time.

5. Caching (three caches, and one thing we refuse to cache)

"Add Redis" is not an answer. The answer is which three things, why each one is a good candidate, and how each is invalidated — and a good candidate always has the same two properties: it's read far more than it's written, and it can tolerate being slightly wrong. Anything that fails the second test doesn't get cached no matter how hot it is.

sitter:1234  ->  { name, photos, bio,
                   rates, services, ... }

raw:  lat=37.77490 lng=-122.41940 r=5mi
      svc=boarding size=large rate<=$60
      2026-08-08→10  sort=rating

key:  sitters:v1:cell=9q8yy:svc=boarding
      :size=lg:rate_bucket=75

Two details that make the search cache actually work

Designing the key for reuse: are we caching geography, or availability too?

This is the question that decides whether the cache is worth having, so answer it explicitly rather than letting it sit implied. The cached value is a geography-and-static-attributes candidate set. Availability is deliberately not in it.

The reason is that the two dimensions change at wildly different rates, and a cache key is only as reusable as its most volatile component. A sitter's location, services, and rates change maybe monthly. Their availability changes every time anyone books them. Worse, availability is queried as a date range, and date ranges are nearly unique per user — one owner wants Aug 8–10, the next wants Aug 9–12, and those are different keys holding almost identical answers. Bake dates into the key and you've built a cache with a hit rate near zero that also goes stale in seconds. Both failure modes at once.

So split the query along its own natural seam.

Stage 1 is expensive and shared. Stage 2 is cheap and personal — it's one indexed query, WHERE sitter_id IN (...) AND during && requested_range, over a few hundred IDs rather than a million. You've moved the volatile dimension out of the cache and onto a query that was always going to be fast. And this keeps the promise from the card below, that availability is read from the source of truth and never cached.

Three techniques for making cache entries reusable

All three techniques are the same idea in different clothes: the cache stores a generous, slow-changing superset, and the request narrows it precisely. Every dimension you can move from the key into the app layer multiplies your hit rate, and the cost is a slightly larger payload and a few lines of filtering — which is a trade you should take almost every time.

A concrete payoff worth stating: because dates left the key, the cached set only goes stale when a sitter joins, leaves, moves, or edits their rates. That's rare, so the TTL rises from ~60 seconds to several minutes, and you can now invalidate it on the CDC event too, since the volatile input is gone. Removing the fast-changing dimension didn't just improve the hit rate, it upgraded the invalidation strategy from "hope" to "event-driven."

sitter:1234:reviews -> { sum: 4820,
                         count: 1043 }
avg = sum / count = 4.62

Invalidation, in one table

6. No double-booking (the Ticketmaster lesson, right-sized)

The core guarantee is that no two active bookings for the same sitter overlap in time. The instinct people reach for is a check-then-insert in application code: read the sitter's bookings, see if the new slot conflicts, and if not, insert. That instinct is wrong, and it's wrong in the exact way Ticketmaster was — between your read and your write, another request can slip in and book the same slot, so two "successful" bookings overlap. The fix is the same principle: make correctness a property the datastore enforces atomically, not something the application hopes it checked in time.

CREATE EXTENSION btree_gist;
ALTER TABLE bookings
  ADD CONSTRAINT no_overlap
  EXCLUDE USING gist (
    sitter_id WITH =,
    during    WITH &&      -- ranges overlap
  ) WHERE (status IN ('pending','confirmed'));

Why shard by sitterId and not by region

Region is the intuitive answer for a geographic product, and it's wrong here. Four reasons, in ascending order of how much they matter.

And the argument for region sharding doesn't survive contact with the architecture anyway. The appeal is locality — keep geographically-near sitters together so proximity searches stay on one shard. But search never touches this database. It goes to Elasticsearch (§4), which maintains its own geo index over a derived copy. The read path that would have benefited from geographic colocation isn't reading from here at all, so region sharding pays every one of the costs above and collects none of the benefit.

Two honest caveats worth volunteering. First, region sharding is right when the driver is data residency rather than performance — if GDPR requires EU sitter data to physically stay in the EU, that's a legal constraint that outranks all of the above, and you'd shard by region at the top level and by sitterId within it. Second, at 5M sitters and 0.4 bookings/sec you wouldn't shard anything yet. Profiles are a few GB and fit comfortably on one primary with read replicas. Bookings are what grow without bound, so bookings are what you shard. Saying "I'd shard bookings by sitterId and leave sitters unsharded until there's a reason" is a stronger answer than sharding everything reflexively.

Notice the constraint uses && on ranges, and the ranges are half-open [start, end). That detail matters: a booking ending at 5:00 and another starting at 5:00 must not count as overlapping, and half-open intervals give you that for free — [3,5) and [5,7) don't overlap, but [3,5) and [4,6) do. Getting the boundary convention right is the difference between a clean "back-to-back bookings are allowed" and an annoying phantom conflict.

"SQL, for ACID properties" is not an answer — and the row-lock trap

When the interviewer asks how you prevent two simultaneous bookings, "I'd use a SQL database for ACID" is the single most common way to lose the point. ACID is a set of properties, not a mechanism. Atomicity says your transaction is all-or-nothing. It says nothing about whether two transactions running concurrently can both decide the sitter is free. At the default READ COMMITTED isolation they absolutely can. Name the mechanism.

The natural next answer is "lock the row," and it contains a trap worth understanding, because it's where a sharp interviewer will take you.

T1: SELECT * FROM bookings
    WHERE sitter_id=1 AND during && '[10:00,11:00)'
    FOR UPDATE;              -- 0 rows returned, 0 rows locked
T2: (same query)             -- 0 rows returned, 0 rows locked
T1: INSERT ...               -- succeeds
T2: INSERT ...               -- also succeeds.  double-booked

Three mechanisms that actually work, ranked

BEGIN;
SELECT id FROM sitters
  WHERE id = 1 FOR UPDATE;   -- real row
SELECT 1 FROM bookings
  WHERE sitter_id=1
    AND during && '[10:00,11:00)';
-- if none:
INSERT INTO bookings ...;
COMMIT;

3 · SERIALIZABLE isolation. Postgres will detect the write skew and abort one of the transactions. Correct, and the most portable of the three in spirit, but it pushes retry logic into every booking path and costs throughput on all transactions to fix a conflict that occurs rarely. Worth naming as the option you considered and rejected on cost.

A fourth, only if you discretized time into fixed slots (§7 option B): a plain UNIQUE (sitter_id, slot_id) index does the whole job, which is exactly the Ticketmaster seat model. That's a real argument in favour of discretization, and worth mentioning when you weigh the schema options.

The gap the constraint does not close

Read the constraint carefully and notice what it never mentions: the availability table. It compares bookings to other bookings, and nothing else. So this insert succeeds.

sitter declares availability:  [08:00, 21:00)
owner books:                   [03:00, 04:00)   -- 3am

No other booking overlaps 3am, so the exclusion constraint is satisfied and the row commits. You've just booked a sitter outside the hours they offered. Your display logic would never have rendered that slot, but display is a UI concern — anything hitting POST /bookings directly bypasses it, as does a retry, a migration script, or an internal admin tool. Never let a correctness rule live only in the code that draws the button.

Booking correctness is therefore two independent checks against two different things, and they need two different mechanisms because they're two different races.

BEGIN;
-- this row EXISTS, so the lock actually locks something
SELECT id FROM availability
 WHERE sitter_id = 1
   AND during @> '[10:00,11:00)'::tstzrange   -- containment
 FOR UPDATE;
-- 0 rows → reject: the sitter never offered this window

INSERT INTO bookings ...;   -- constraint handles booking-vs-booking
COMMIT;

Note the two operators differ, and not by accident. && is overlap, used by the constraint because any intersection between two bookings is a conflict. @> is containment, used here because the booking must fit entirely inside the offered window. That's §7's "overlap for search, containment for booking" rule appearing as literal SQL.

Handling the rejection

When the constraint rejects an insert, Postgres raises SQLSTATE 23P01 (exclusion_violation). Catch that specific code and return a clean 409 Conflict with refreshed availability attached, so the client can re-render immediately. Letting it propagate as a generic exception gives the owner a 500 for what is a completely normal, expected outcome in a marketplace — and a 500 will also trip your alerting, so every lost race becomes a page. Distinguishing expected conflicts from actual failures is a small detail that reads as production experience.

7. Deep dive — modeling and displaying time slots

This is the follow-up: an owner searches for "1/1 4–6pm," a sitter offers "1/1 3–5pm," and you have to decide how to store, match, and display these. It's deceptively deep, and the schema choice drives everything downstream.

Three schema options, with the trade-offs

C · Recurring rules. Store "available weekdays 9–5" as an RRULE plus exception overrides. Compact for regular schedules and matches how sitters actually think, but you still have to expand it into option A or B to check a concrete date, so it's a layer on top, not a replacement.

Matching: overlap for search, containment for booking

Here's the subtlety in your example. The owner wants 4–6pm; the sitter is available 3–5pm. These overlap (4–5pm is common), but the sitter cannot cover the whole 4–6pm request. So the matching rule depends on the intent. For search/browse, overlap is the right filter — show sitters who have any availability intersecting the owner's window, because the owner may be flexible. For an actual booking, you need containment: the requested range must fit entirely inside an availability window minus existing bookings. 4–6pm is not contained in 3–5pm, so this sitter is not a full match for that booking.

What you show is then a product decision worth naming out loud. You can exclude partial matches, or you can surface them helpfully — "This sitter is free 4–5pm of your 4–6pm request" — and let the owner shorten their window or pick another sitter. Displaying the partial overlap rather than silently dropping the sitter is usually the better marketplace experience.

The rule maps directly onto two Postgres range operators, which is a satisfying place for it to land.

search:   av.during && :requested    -- overlap:     any intersection counts
booking:  av.during @> :requested    -- containment: must fit entirely inside

And note that containment is a rule you have to enforce, not merely one you match on. The exclusion constraint in §6 compares bookings to other bookings and never looks at availability, so containment needs its own check — a FOR UPDATE lock on the availability row, inside the booking transaction. §6 works through why that lock is the right tool here and the wrong tool for the double-booking half.

The timezone gotcha, and the display format

"1/1 4–6pm" is ambiguous until you attach a timezone, and this is the correctness bug that bites financial and scheduling apps alike. Store everything as timezone-aware timestamps (UTC under the hood), and render in the relevant local zone — the walk happens at the sitter's wall-clock time, so that's usually the anchor. On screen, show the human form the owner typed ("Fri Jan 1, 4:00–6:00 PM") plus the duration, and keep the machine form (the range, in UTC) for matching. The half-open convention from §6 carries into display too: a 4–6pm slot and a 6–8pm slot are adjacent, not overlapping, so a sitter can legitimately offer both back to back.

Do not store remaining availability — derive it

This is the design decision that separates the two versions of this system, and it's easy to get wrong because the wrong answer feels more "efficient." The question is what happens to a sitter's availability row when a booking lands inside it.

availability: [08:00, 21:00)
  ↓ booking arrives 10:00–11:00
availability: [08:00, 10:00)
              [11:00, 21:00)   -- split, stored

base:     [08:00, 21:00)      -- never mutated
bookings: [10:00, 11:00)
free   =  base MINUS bookings
       =  [08:00,10:00), [11:00,21:00)

The argument that wins it is cancellation, which is the operation people forget to test their schema against. Under the stored design, cancelling the 10–11 booking means locating the two rows you split, merging them back into one contiguous [08:00, 21:00), handling the case where only one side is adjacent, and doing all of it transactionally. Under the derived design, cancellation is flipping one status field — availability is instantly correct again because it was never a separate fact that could be wrong.

The one legitimate exception, and it's already in the architecture. Elasticsearch does need materialized availability, because you cannot efficiently filter on a value computed per query. That's fine, because it's an explicitly derived read model that's allowed to be seconds stale and gets rebuilt by CDC. So the line to draw is: materialize for search, where staleness is acceptable and the booking step re-checks; compute for booking, where it isn't. A worker updating the search index's availability is correct. That same worker updating the availability that booking decisions read is a race condition — between the booking committing and the worker running, the source of truth says the sitter is still free.

Watch for this in your own diagrams. If an async worker consuming from a queue is what updates availability, and the booking path reads that availability, you have made your consistency-critical data eventually consistent with itself. The exclusion constraint can still save you if bookings are what it checks — but if the check reads a worker-maintained table, nothing does.

8. Deep dive — holding a booking for 10 minutes

The second follow-up: when the owner presses Book, hold the slot for 10 minutes while they finish checkout, then either confirm or release it. This is the Ticketmaster reserve-then-pay pattern, and the interesting part is doing it without a second reservation store and without breaking the §6 constraint.

The two-step flow

POST /bookings   (press "Book")
  Idempotency-Key: <uuid>
  → insert Booking
      status = 'pending',
      reserved_until = now() + 10 min
  → 201 { bookingId, reserved_until }

POST /bookings/{id}/confirm  (after pay)
  → if reserved_until > now():
       status = 'confirmed',
       reserved_until = null
     else: 409 hold expired

Expiring holds: a sweeper plus lazy reclaim

First, a small background job runs every minute or so and flips pending rows whose reserved_until < now() to expired, which removes them from the constraint's active set and frees the slot. That bounds staleness to roughly a minute. Second, and more precise, lazy reclaim on conflict: when a new booking insert fails the exclusion constraint, check whether the blocking row is a pending hold that's already past its reserved_until. If it is, expire it and retry the insert inside the same transaction. Together, the sweeper keeps the table tidy and the lazy path gives an instant, correct answer the moment someone actually wants the slot — you never make a real customer wait a minute for a dead hold to clear.

This is deliberately the same "one reservation store, reclaimed by a timestamp plus a sweeper" design we chose over a Redis TTL in the seat-assignment discussion. A Redis key with a 10-minute TTL would auto-expire with no sweeper, which sounds tempting, but it reintroduces two sources of truth that can drift — the Redis key gone while the DB row lingers, or vice versa. At this contention level the pure-database approach is simpler and has one source of truth, so prefer it and say why. You'd only reach for the Redis hold if booking volume on a single hot slot got high enough that the constraint became a bottleneck, which it won't here.

What the client does

The Book press carries a client-generated idempotency key so a double-tap or a network retry creates one hold, not two — and that same key flows to the payment provider on confirm so a retry never double-charges. The UI shows a countdown timer from reserved_until, and on expiry it tells the owner the hold lapsed and re-checks availability rather than letting them pay for a slot they no longer have. If they confirm in time, the hold flips to a real booking; if they abandon, the sweeper reclaims it silently.

The variant to be ready for: what if booking is a single transaction?

If the interviewer removes payment confirmation and says booking is one atomic operation, the correct answer is yes, delete reserved_until entirely — along with the pending status, the expired status, the sweeper job, and the lazy-reclaim branch. Answer decisively rather than hedging, because the whole hold apparatus exists to solve exactly one problem, and that problem just went away.

The reasoning to say out loud: a hold is the price you pay for a gap between deciding and committing. The two-step flow exists because the owner picks a slot, then goes away to a payment provider for an unbounded amount of time, and during that gap the slot must be neither free (someone else takes it) nor permanently taken (they might abandon). reserved_until is what makes an incomplete booking visible to the exclusion constraint while it's still incomplete. Collapse the gap to a single transaction and there is no incomplete state to represent. The insert either commits or it doesn't.

9. Extension: recurring walks, pickup/dropoff, lockbox codes

Three changes. The reassuring part is that the core booking machinery doesn't move — each walk is just another booking flowing through the same overlap constraint, which is the sign the model was factored well.

10. Service boundaries — are microservices right here?

The diagram in §2 shows separate search, booking and availability services, so be ready to defend that split rather than treating it as the default shape of a system. The generic pros-and-cons list ("independent deployment, but operational overhead") scores nothing. Argue it from this system.

The rule to state, which generalizes past this problem: anything that must be in one transaction goes in one service. Draw the transaction boundaries first and let the service boundaries fall around them. If a proposed split would force a distributed transaction, the split is wrong — merge those services and find a different seam. Search versus booking passes that test cleanly, because the only thing crossing it is a CDC stream that was always asynchronous.

The pragmatic close, worth saying because interviewers are listening for judgement rather than dogma: "I'd draw these three boxes because they scale and fail differently, but I'd keep booking and availability in one database so the overlap constraint survives. At this scale I'd honestly start with a monolith plus Elasticsearch and split search out when its traffic justified it — the boundary I've drawn is the one I'd extract first, not the one I'd start with."

11. Follow-ups — answers to have ready

How do you prevent two simultaneous bookings? (the question this design exists to answer)

An exclusion constraint on (sitter_id, during) over active statuses. The reason I prefer it to locking is that it removes the check-then-act entirely rather than protecting it, so there's no window to race in and no way for a future code path to bypass the rule. If the database lacked range types I'd take SELECT FOR UPDATE on the sitter row — deliberately not on the bookings query, because that query returns zero rows in the racing case and locking zero rows locks nothing, so phantoms slip straight through. SERIALIZABLE is a third option that works but pushes retry logic into every booking path to solve a conflict that's rare here. And I'd avoid answering this with "SQL, for ACID" — ACID doesn't say concurrent transactions can't both read "free" and both insert, which at READ COMMITTED is exactly what happens.

Your constraint stops overlapping bookings. What stops someone booking a sitter at 3am?

Nothing, as written — that's a real gap and worth catching before the interviewer does. The exclusion constraint compares bookings to other bookings and never references availability, so a 3am booking satisfies it as long as no other booking overlaps 3am. Correctness is actually two checks. Booking-versus-booking is the constraint, because the conflicting row doesn't exist yet and can't be locked. Booking-versus-availability is a SELECT … FOR UPDATE on the availability row using the containment operator @>, which works precisely because that row does exist and the lock stops the sitter withdrawing the window mid-transaction. Same tool, opposite verdict on the two halves. And I'd never rely on the UI not rendering the slot, because anything hitting the endpoint directly bypasses that.

What does the client see when it loses the race?

Postgres raises SQLSTATE 23P01, exclusion violation. I'd catch that code specifically and return a 409 with refreshed availability so the client re-renders straight away. If it propagates as a generic exception the owner gets a 500 for a completely expected marketplace outcome, and it trips alerting too, so every lost race becomes a page. Separating expected conflicts from real failures matters as much for on-call as for UX.

A booking lands in the middle of an availability window. What happens to that window?

Nothing. I don't store remaining availability, I derive it as base availability minus bookings at read time. The reason is cancellation: if I materialize the split I now need a correct merge to undo it, plus a correct rewrite for every modification, and two tables that must agree forever. Deriving it means booking inserts one row and touches nothing else, and cancelling flips one status field with availability instantly correct again. It's the same instinct as the exclusion constraint — rather than implementing a guarantee that two facts agree, remove the second fact. The exception is the search index, which does need materialized availability because you can't filter efficiently on a computed value. That's fine because it's an explicitly stale derived read model. Materialize for search, compute for booking.

Your worker updates availability off a Kafka queue. Is that safe?

Safe for the search index, not safe if booking decisions read it. Between the booking committing and the worker running, that table still says the sitter is free, so a second booking passes its check — I'd have made my consistency-critical data eventually consistent with itself, which defeats the whole point. The async path is the right place for search updates, emails and incrementing the sit counter, because all of those tolerate seconds of lag. The availability that the booking transaction checks has to be in the same database as the booking insert, which is also why I wouldn't split booking and availability into separate services.

Why shard by sitter ID instead of by region? Region seems natural for a geo product.

Three reasons, and one of them is decisive. Region skews badly — the Bay Area would carry orders of magnitude more load than Montana, and time zones make each region shard peak separately so you provision for every local peak instead of the global average. Boundary sitters with a travel radius serve owners on both sides of a line, so region forces fan-out or duplication for exactly those cases. But the decisive one is that the shard key defines my transaction boundary, so it has to be immutable. The exclusion constraint only works if all of a sitter's bookings are colocated. Sitter ID guarantees that permanently. Region guarantees it until a sitter moves house, and then their booking history has to migrate across shards, during which the constraint can't be enforced. I'd also point out that the usual argument for region sharding is geographic locality for search, and search doesn't read this database at all — it reads Elasticsearch. So region pays every cost and collects no benefit. The exception is data residency: if EU data must stay in the EU, that's a legal requirement that outranks all of this.

Does your search cache store availability, or just geography?

Just geography plus the static filters. Availability is applied live afterward. The reason is that a cache key is only as reusable as its most volatile component, and availability is both the fastest-changing field and the one queried as a near-unique date range per user. Putting dates in the key gives you a near-zero hit rate and entries that go stale in seconds. So the cached value is a candidate set of sitter IDs for a cell plus service plus rate bucket, and then one indexed query filters those few hundred IDs by the requested range. That also keeps my earlier promise that availability is never cached.

How do you get more users sharing the same cache entries?

Three moves, all the same idea. Cache units smaller than the question — one entry per geo cell rather than per radius, so a 25-mile search unions cells that a 5-mile search already warmed. Bucket every continuous dimension, rounding price and radius up to boundaries so "under $60" and "under $65" hit one entry, always rounding toward a superset so the app layer can filter down without hiding valid results. And drop filters that barely narrow anything, since a filter matching 90% of sitters halves your reuse while removing almost nothing. The general principle is that the cache holds a generous slow-changing superset and the request narrows it precisely.

If booking were a single transaction with no payment step, would you still need reserved_until?

No, and I'd delete the pending and expired statuses, the sweeper and the lazy reclaim with it. A hold exists purely to represent a decision that's been made but not yet committed, and it's the gap created by the payment provider that makes that state necessary. One transaction, no gap, no state. I'd keep status because cancellation is unrelated to holds, and I'd keep the idempotency key because a double-tap can still create two inserts. Then I'd flag that the assumption is unrealistic for a real marketplace: sitter approval alone brings the pending state straight back, just with a timeout measured in hours instead of ten minutes.

Would you build this as microservices?

I'd split search from booking, because they scale, fail and stay consistent differently, and the seam matches a data-store boundary I already needed. I would not split booking from availability, and that's the more interesting half of the answer — those two must share a database or the exclusion constraint disappears and I'm replacing an atomic guarantee with a saga and a reconciliation job. The rule I'd state is to draw service boundaries around transaction boundaries rather than around nouns. And at roughly 4 searches per second I'd be honest that a modular monolith plus Elasticsearch is probably the right starting point, with search as the first thing I'd extract when its traffic justified it.

Why Elasticsearch rather than PostGIS, when your data is already in Postgres?

Because the query isn't only geographic. It's proximity plus categorical filters plus ranking, and Elasticsearch resolves all three in one engine pass over a denormalized document. PostGIS would handle the radius well but push filtering and relevance ranking back into application code, and the sitter document spans four tables so I'd be joining on every search. The cost I'm accepting is a second store to keep in sync and eventual consistency measured in seconds. If the requirement had been "five nearest sitters, no filters," I'd have taken PostGIS and skipped the pipeline entirely.

What does "refresh" mean in your CDC arrow?

Two things, and I'd separate them. Pipeline-wise it's Debezium tailing the Postgres WAL, a consumer rebuilding the affected sitter's document, and a bulk upsert. Elasticsearch-wise, "refresh" is a specific term: an indexed doc sits in an in-memory buffer and isn't searchable until a refresh flushes it into a new Lucene segment. That defaults to one second, which is why Elasticsearch is near-real-time. I'd raise refresh_interval to 30s during backfills, and use refresh=wait_for on the path where a sitter edits their profile and immediately views it.

A sitter changes their nightly rate. What has to happen?

The rate lives in three places, so three things fire off one CDC event. The Elasticsearch document is re-emitted whole, because the doc is denormalized and I can't patch one field independently of the join that produced it. The sitter profile cache is updated in place. The search results cache is not touched — I can't enumerate which keys contain that sitter, so it ages out on its 60-second TTL. That last one is the interesting answer: I'd rather serve a one-minute-stale price in a result list and show the true price on the profile than build a tag-based invalidation scheme that evicts half the cache on every edit.

Why not cache availability? It's the hottest read you have.

Because it's the one piece of data that has to be right. A cached "available" that's actually booked is a double-booking, which is precisely the failure §6's exclusion constraint exists to make impossible — caching it would reintroduce the bug at a different layer. The general rule I'd apply: cache what tolerates being slightly wrong, and read the source of truth for what doesn't. Stale in search is fine because booking re-checks; stale at write time is not.

Your search cache key is a rounded geo cell. Doesn't that give wrong distances?

It would if I returned the cached set directly. Instead I query Elasticsearch with a padded radius, cache that superset keyed by the cell, and then compute exact distances and re-sort in the application layer per request. The cache saves the engine round trip, the app layer restores precision. Without the quantization the hit rate is near zero, because no two users share a raw lat/long.

Why don't you need Redis locks or a waiting queue like Ticketmaster?

Contention. Ticketmaster had ten million people racing for one event, a genuine hot key, so it needed a queue to shed load and locks to serialize. Here a single sitter is booked by at most a handful of owners and rarely at the same instant, so the row-level exclusion constraint fully serializes the rare conflict with no extra infrastructure. Naming that I considered the heavier tools and rejected them on the scale read is the point.

Two owners press Book for the same slot at the same moment — walk the race.

Both try to insert a pending booking for that sitter with overlapping ranges. The exclusion constraint lets the first commit and rejects the second atomically. The loser gets a clean "just taken" and re-queries. Because the hold is a real row covered by the same constraint, there's no window between check and insert to race in.

The owner's 4–6pm request only partially fits a 3–5pm sitter. What happens?

For search I filter by overlap, so the sitter shows up. For booking I require containment, so 4–6pm is rejected against 3–5pm. In the UI I'd surface the partial fit ("free 4–5pm of your window") and let the owner shorten their request or pick another sitter, rather than silently dropping them — better marketplace UX.

What clears an abandoned 10-minute hold, and how fast?

Two things. A sweeper every minute flips expired pending holds to expired, bounding staleness to ~a minute. And lazy reclaim: the next person who tries the slot triggers an immediate expire-and-retry if the blocker is a dead hold, so a real customer never waits. Expiry can't live in the constraint because its predicate must be immutable and now() isn't.

Why store time as a range instead of start/end columns?

Because a range is a value the database can compare for overlap and containment directly, which is what makes the exclusion constraint and the matching queries clean. Two loose columns push all that logic into application code, where it's easy to get boundary conditions wrong. Half-open [start,end) ranges also make back-to-back slots not-overlap for free.

Does a recurring MWF series need distributed transactions?

No. Each occurrence is a separate booking for one sitter, and all of a sitter's bookings live on the same shard, so materializing the series is a set of single-shard inserts, each independently checked by the constraint. If one date conflicts, only that instance fails and you surface it for the owner to adjust.

12. Numbers to drop

13. 30-second recap script